﻿-- =========================================================================================================
-- Author:		MICHAEL RITACCO
-- Copyright:	2010 MEKHANO GROUP LLC
-- Version:		08/21/2010 1.0
-- =========================================================================================================
CREATE PROCEDURE [flcr].[api_service_config_sel]
	@SERVICE_CONFIG_ID INT = NULL
	,@AUDIT_USER VARCHAR(100) = NULL
	,@AUDIT_DTTM DATETIME = NULL
	,@AUDIT_DTTM_TZ INT = NULL
	,@AUDIT_MOD_USER VARCHAR(100) = NULL
	,@AUDIT_MOD_DTTM DATETIME = NULL
	,@AUDIT_MOD_DTTM_TZ INT = NULL
	,@BUILTIN BIT = NULL
	,@ACTIVE_FLG BIT = NULL
	,@SERVICE_ID INT= NULL
	,@SERVICE_DOMAIN_ID INT = NULL
	,@DEVICE_ID INT = NULL
	,@DEVICE_DOMAIN_ID INT = NULL
	,@ENV_ID INT = NULL
	
AS
BEGIN
	SELECT A.[SERVICE_CONFIG_ID]
		  ,A.[AUDIT_USER]
		  ,A.[AUDIT_DTTM]
		  ,A.[AUDIT_DTTM_TZ] 
		  ,A.[AUDIT_MOD_USER]
		  ,A.[AUDIT_MOD_DTTM]
		  ,A.[AUDIT_MOD_DTTM_TZ] 
		  ,A.[BUILTIN]
		  ,A.[WRKFL_ITEM_ID]
		  ,A.[WRKFL_STATUS_ID]
		  ,A.[ACTIVE_FLG]
		  ,A.[SORT_ORDER]
		  ,A.[SERVICE_DEVICE_XREF_ID]
		  ,B.[SERVICE_ID]
		  ,E.[DOMAIN_NAME] AS SERVICE_DOMAIN_NAME 
		  ,C.[SERVICE_NAME]
		  ,C.[SERVICE_SNAME] 
		  ,D.[DEVICE_ID]
		  ,E.[DOMAIN_NAME] AS DEVICE_DOMAIN_NAME 
		  ,D.[DEVICE_ASSET_NAME]
		  ,D.[DEVICE_SERIAL_NUM] 
		  ,D.[DEVICE_ASSET_TAG]
		  ,G.[ENV_ID]
		  ,G.[ENV_NAME]
		  ,A.[SERVICE_CONFIG_PROP_ID]
		  ,I.[SERVICE_PROP_TYPE_NAME] AS PNAME
		  ,J.[SERVICE_PROP_TYPE_NAME] AS PVALUE
		  ,A.[SERVICE_CONFIG_PROP_VALUE]
		  ,A.[PARENT_ID]
		  ,A.[AUTOCOLLECT_FLG]
		  ,A.[AUTOCOLLECT_MODULE_ID]
		  ,A.[AUTOCOLLECT_FDTTM]
		  ,A.[AUTOCOLLECT_LDTTM]
		  ,A.[AUTOCOLLECT_AUDIT_USER]
	  FROM [flcr].[SERVICE_CONFIG]  A
	 INNER JOIN [flcr].[SERVICE_DEVICE_XREF] B
		ON	A.[SERVICE_DEVICE_XREF_ID] = B.[SERVICE_DEVICE_XREF_ID]
	 INNER JOIN [flcr].[SERVICE] C
		ON	B.[SERVICE_ID] = C.[SERVICE_ID]
	 INNER JOIN [flcr].[DEVICE] D 
		ON	B.[DEVICE_ID] = D.[DEVICE_ID]
	 INNER JOIN [flcr].[DOMAIN] E
		ON	C.[DOMAIN_ID] = E.[DOMAIN_ID]
	 INNER JOIN [flcr].[DOMAIN] F
		ON	D.[DOMAIN_ID] = F.[DOMAIN_ID]
	 INNER JOIN [flcr].[ENVIRONMENT] G
		ON	B.[ENV_ID] = G.[ENV_ID]
	 INNER JOIN [flcr].[SERVICE_CONFIG_PROPERTY] H
		ON	A.[SERVICE_CONFIG_PROP_ID] = H.[SERVICE_CONFIG_PROP_ID]
	 INNER JOIN [flcr].[SERVICE_PROPERTY_TYPE] I
		ON	H.[PNAME_SERVICE_PROP_TYPE] = I.[SERVICE_PROP_TYPE]
	 INNER JOIN [flcr].[SERVICE_PROPERTY_TYPE] J
		ON	H.[PVALUE_SERVICE_PROP_TYPE] = J.[SERVICE_PROP_TYPE]
	 WHERE	A.[SERVICE_CONFIG_ID] = COALESCE(@SERVICE_CONFIG_ID, A.[SERVICE_CONFIG_ID])
	   AND	A.[AUDIT_USER] = COALESCE(@AUDIT_USER, A.[AUDIT_USER])
	   AND	A.[AUDIT_DTTM] = COALESCE(@AUDIT_DTTM, A.[AUDIT_DTTM])
	   AND	A.[AUDIT_DTTM_TZ] = COALESCE(@AUDIT_DTTM_TZ, A.[AUDIT_DTTM_TZ])
	   AND	A.[AUDIT_MOD_USER] = COALESCE(@AUDIT_MOD_USER, A.[AUDIT_MOD_USER])
	   AND	A.[AUDIT_MOD_DTTM] = COALESCE(@AUDIT_MOD_DTTM, A.[AUDIT_MOD_DTTM])
	   AND	A.[AUDIT_MOD_DTTM_TZ] = COALESCE(@AUDIT_MOD_DTTM_TZ, A.[AUDIT_MOD_DTTM_TZ])
	   AND	A.[BUILTIN] = COALESCE(@BUILTIN, A.[BUILTIN])	   
	   AND	A.[ACTIVE_FLG] = COALESCE(@ACTIVE_FLG, A.[ACTIVE_FLG])
	   AND	G.[ENV_ID] = COALESCE(@ENV_ID, G.[ENV_ID])
	   AND	C.[SERVICE_ID] = COALESCE(@SERVICE_ID, C.[SERVICE_ID])	   		
	   AND	E.[DOMAIN_ID] = COALESCE(@SERVICE_DOMAIN_ID, E.[DOMAIN_ID])
	   AND	D.[DEVICE_ID] = COALESCE(@DEVICE_ID, D.[DEVICE_ID])
	   AND	E.[DOMAIN_ID] = COALESCE(@DEVICE_DOMAIN_ID, E.[DOMAIN_ID])
END